package com.cal.utms.dao.dayEnd;

import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;

import com.cal.utms.dto.dayEnd.DayEndDto;
import com.cal.utms.entity.CustomerInvestment;
import com.cal.utms.entity.CustomerRedemptionRequest;
import com.cal.utms.entity.DailyFigure;
import com.cal.utms.entity.Deal;
import com.cal.utms.entity.DealDailyInterest;
import com.cal.utms.entity.DealInstrumentMap;
import com.cal.utms.entity.Fund;
import com.cal.utms.entity.InstrumentFloatingRate;
import com.cal.utms.entity.MoneyTransaction;
import com.cal.utms.entity.Payment;
import com.cal.utms.entity.Receipt;
import com.cal.utms.enumType.InterestRate;
import com.cal.utms.util.HibernateUtil;

public class DayEndDao {

	@SuppressWarnings("unchecked")
	public List<Object[]> getNonzeroMoneyTransactions(int fundId, String custType, Date valueDate, DayEndDto dayEndDto) {
		String sql;
		//String sql = "SELECT tran_to, sum(tran_amont) as sum FROM money_transaction WHERE customer_type=:customerType AND related_fund_id=:fundId GROUP BY tran_to HAVING sum(tran_amont)!=0";
		if(custType.equals("CUS")){
			sql = "SELECT concat(c.initials, '.', c.surname), sum(mt.tran_amont) as sum FROM money_transaction mt, customer c WHERE c.id=mt.tran_to AND mt.customer_type='CUS' AND mt.related_fund_id=:fundId and mt.affect_date<=:valueDate GROUP BY mt.tran_to HAVING sum(mt.tran_amont)!=0";
		}else if(custType.equals("ISU")){
			sql = "SELECT c.name, sum(mt.tran_amont) as sum FROM money_transaction mt, issuer c WHERE c.id=mt.tran_to AND mt.customer_type='ISU' AND mt.related_fund_id=:fundId and mt.affect_date<=:valueDate GROUP BY mt.tran_to HAVING sum(mt.tran_amont)!=0";			
		}else if(custType.equals("CTO")){
			sql = "SELECT c.name, sum(mt.tran_amont) as sum FROM money_transaction mt, custodian c WHERE c.id=mt.tran_to AND mt.customer_type='CTO' AND mt.related_fund_id=:fundId and mt.affect_date<=:valueDate GROUP BY mt.tran_to HAVING sum(mt.tran_amont)!=0";
		}else{
			dayEndDto.setStatus("Invalid Customer Type :"+custType);
			dayEndDto.setSuccess(false);
			return null;
		}
		/*
		 * 
		 * SELECT concat(c.initials, c.surname), sum(mt.tran_amont) as sum FROM money_transaction mt, customer c WHERE c.id=mt.tran_to AND mt.customer_type='CUS' AND mt.related_fund_id=3 GROUP BY mt.tran_to HAVING sum(mt.tran_amont)!=0;


SELECT c.name, sum(mt.tran_amont) as sum FROM money_transaction mt, issuer c WHERE c.id=mt.tran_to AND mt.customer_type='ISU' AND mt.related_fund_id=24 GROUP BY mt.tran_to HAVING sum(mt.tran_amont)!=0;

SELECT c.name, sum(mt.tran_amont) as sum FROM money_transaction mt, custodian c WHERE c.id=mt.tran_to AND mt.customer_type='CTO' AND mt.related_fund_id=22 GROUP BY mt.tran_to HAVING sum(mt.tran_amont)!=0;

SELECT 'CALI', sum(mt.tran_amont) as sum FROM money_transaction mt WHERE mt.customer_type='CALI' GROUP BY mt.tran_to HAVING sum(mt.tran_amont)!=0;
		 * 
		 */
		List<Object[]> data = null;
		
		Transaction tx = null;
		Session session = null;
		try {
			session = HibernateUtil.getSession().openSession();
			tx = session.beginTransaction();
			
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("fundId", fundId);
			query.setDate("valueDate", valueDate);
			//query.setParameter("customerType", custType);
			data = query.list();
			tx.commit();
		} catch (HibernateException e) {
			if (tx != null)
				tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		return data;
	}
	
	@SuppressWarnings("unchecked")
	public List<MoneyTransaction> getDailyMoneyTransForFund(Fund fund, Date valueDate, DayEndDto dayEndDto){
		Transaction tx = null;
		List<MoneyTransaction> res = null;

		Session session = null;
		try {
			session = HibernateUtil.getSession().openSession();
			tx = session.beginTransaction();
			Query query = session.createQuery("SELECT m FROM MoneyTransaction m WHERE m.fund=:fund AND m.customerType='FUN' AND m.effectDate=:effectDate");
			query.setParameter("fund", fund);
			query.setParameter("effectDate", valueDate);
			//query.setParameter("insType", insTypeId);
			res = (List<MoneyTransaction>) query.list();
			tx.commit();	
		} catch (HibernateException e) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getDailyMoneyTransForFund Fund: "+fund.getFundCode()+" ValueDate: "+valueDate+" :"+ e.getMessage());
			e.printStackTrace();
		} finally {
			try{
				if(session!=null)
					session.close();
			}catch(Exception e){
				System.out.println("Session close Error!");
			}
			
		}
		return res;
	}
	
	public void getRemainingMoneyTransactions(int fundId, String custType, Date valueDate, DayEndDto dayEndDto){
		String result = "";
		List<Object[]> data = getNonzeroMoneyTransactions(fundId, custType, valueDate, dayEndDto);
		if(data == null){
			return ;
		}
		for (Object[] res : data) {
			result = result+";"+res[0]+","+res[1];
		}
		dayEndDto.setSuccess(true);
		dayEndDto.setResultString(result);
	}

	@SuppressWarnings("unchecked")
	public void getPendingInstrumentReceive(int fundId, Date valueDate, DayEndDto dayEndDto){
		String sql;
		sql = "select dt.deal_type, d.deal_code, i.instrument_code, dim.status from instrument i, deal d, deal_type dt, deal_instrument_map dim where dim.deal_id=d.id and dim.instrument_id=i.id and d.type_id=dt.id and (dt.code='PURCH' or dt.code='RREPO') and d.fund_id=:fundId and d.value_date<=:valueDate and d.maturity_date>:valueDate and (dim.status='Not Received From Customer' or dim.status='Received From Customer')";
		sql = sql+" union ";
		sql = sql + "select dt.deal_type, d.deal_code, i.instrument_code, dim.status from instrument i, deal d, deal_type dt, deal_instrument_map dim where dim.deal_id=d.id and dim.instrument_id=i.id and d.type_id=dt.id and dt.code='REPO' and d.fund_id=:fundId and d.maturity_date<=:valueDate and (dim.status='Issued by Custodian' or dim.status='Dispatched to Customer')";

		List<Object[]> data = null;
		String resul = "";
		
		Transaction tx = null;
		Session session = null;
		try {
			session = HibernateUtil.getSession().openSession();
			tx = session.beginTransaction();
			
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("fundId", fundId);
			query.setDate("valueDate", valueDate);
			data = query.list();
			tx.commit();
		} catch (HibernateException e) {
			if (tx != null)
				tx.rollback();
			e.printStackTrace();
			dayEndDto.setSuccess(false);
			dayEndDto.setStatus("Database Error");
			return;
		} finally {
			session.close();
		}
		//System.out.println("Data size:"+data.size());
		for (Object[] res : data) {
			resul = resul+ res[0]+","+res[1]+","+res[2]+","+res[3]+";";
		}
		dayEndDto.setResultString(resul);
		dayEndDto.setSuccess(true);
	}
	
	
	
	@SuppressWarnings("unchecked")
	public void getPendingInstrumentIssue(int fundId, Date valueDate, DayEndDto dayEndDto){
		String sql;
		sql = "select dt.deal_type, d.deal_code, i.instrument_code, dim.status from instrument i, deal d, deal_type dt, deal_instrument_map dim where dim.deal_id=d.id and dim.instrument_id=i.id and d.type_id=dt.id and dt.code='SALE' and d.fund_id=:fundId and d.value_date<=:valueDate and (dim.status='Not Received From Custodian' or dim.status='Issued by Custodian')";
		sql = sql + " union ";
		sql = sql + "select dt.deal_type, d.deal_code, i.instrument_code, dim.status from instrument i, deal d, deal_type dt, deal_instrument_map dim where dim.deal_id=d.id and dim.instrument_id=i.id and d.type_id=dt.id and (dt.code='SALE' or dt.code='REPO') and d.fund_id=:fundId and d.value_date<=:valueDate and d.maturity_date>:valueDate and (dim.status='Not Received From Custodian' or dim.status='Issued by Custodian')";
		sql = sql +" union ";
		sql = sql + "select dt.deal_type, d.deal_code, i.instrument_code, dim.status from instrument i, deal d, deal_type dt, deal_instrument_map dim where dim.deal_id=d.id and dim.instrument_id=i.id and d.type_id=dt.id and dt.code='RREPO' and d.fund_id=:fundId and d.maturity_date<=:valueDate and (dim.status='Not Issued by Custodian' or dim.status='Issued by Custodian' or dim.status='Dispatched to Custodian')";

		List<Object[]> data = null;
		String resul = "";
		
		Transaction tx = null;
		Session session = null;
		try {
			session = HibernateUtil.getSession().openSession();
			tx = session.beginTransaction();
			
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("fundId", fundId);
			query.setDate("valueDate", valueDate);
			data = query.list();
			tx.commit();
		} catch (HibernateException e) {
			if (tx != null)
				tx.rollback();
			e.printStackTrace();
			dayEndDto.setSuccess(false);
			dayEndDto.setStatus("Database Error");
			return;
		} finally {
			session.close();
		}
		//System.out.println("Data size:"+data.size());
		for (Object[] res : data) {
			resul = resul+ res[0]+","+res[1]+","+res[2]+","+res[3]+";";
		}
		dayEndDto.setResultString(resul);
		dayEndDto.setSuccess(true);
	}
	

	public double getMoneyTransSumForEod(int fundId, Date valueDate){
		String sql;
		double sumTran = 0;
		BigDecimal sumObj = null;
		sql = "SELECT sum(mt.tran_amont) FROM money_transaction mt WHERE mt.customer_type='FUN' AND mt.tran_to=:fundId AND mt.affect_date<=:valueDate";

		Transaction tx = null;
		Session session = null;
		try {
			session = HibernateUtil.getSession().openSession();
			tx = session.beginTransaction();
			
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("fundId", fundId);
			query.setDate("valueDate", valueDate);
			sumObj = (BigDecimal) query.uniqueResult();
			tx.commit();
		} catch (HibernateException e) {
			if (tx != null)
				tx.rollback();
			e.printStackTrace();
			return -1;
		} finally {
			session.close();
		}
		if(sumObj!=null){
			sumTran = sumObj.doubleValue();
		}
		return sumTran;
	}
	
	
	public double getUnitSumForEod(int fundId, Date valueDate){
		String sql;
		BigDecimal sumInvestObj = null;
		BigDecimal sumRedempObj = null;
		double sumInvest=0;
		double sumRedemp=0;
		double sumAll = 0;

		Transaction tx = null;
		Session session = null;
		try {
			session = HibernateUtil.getSession().openSession();
			tx = session.beginTransaction();
			
			sql = "SELECT sum(no_of_issued_unit) FROM customer_investment WHERE fund_id=:fundId AND status='CONFIRM' AND issued_date<=:valueDate";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("fundId", fundId);
			query.setDate("valueDate", valueDate);
			sumInvestObj = (BigDecimal) query.uniqueResult();
			
			sql = "SELECT sum(tot_unit) FROM customer_redemption_request WHERE fund_id=:fundId AND status='CONFIRM' AND expected_date<=:valueDate";
			query = session.createSQLQuery(sql);
			query.setParameter("fundId", fundId);
			query.setDate("valueDate", valueDate);
			sumRedempObj = (BigDecimal) query.uniqueResult();
			
			tx.commit();
		} catch (HibernateException e) {
			if (tx != null)
				tx.rollback();
			e.printStackTrace();
			return -1;
		} finally {
			session.close();
		}
		if(sumInvestObj!=null){
			sumInvest = sumInvestObj.doubleValue();
		}
		if(sumRedempObj!=null){
			sumRedemp = sumRedempObj.doubleValue();
		}
		//System.out.println(sumAll+"="+sumInvest+"-"+sumRedemp);
		sumAll = sumInvest-sumRedemp;
		return sumAll;
	}
	
	
	@SuppressWarnings("unchecked")
	public void queryPendingInvestments(int fundId, Date valueDate, DayEndDto dayEndDto){
		List<CustomerInvestment> resList = null;
		Session session = null;
		try{
			session = HibernateUtil.getSession().openSession();
			Criteria cr = session.createCriteria(CustomerInvestment.class);
			
			cr.createAlias("fund", "fund");
			ProjectionList projectionList = Projections.projectionList();
			projectionList.add(Projections.property("invenstmentCode"), "invenstmentCode");
			projectionList.add(Projections.property("customer"), "customer");
			projectionList.add(Projections.property("amount"), "amount");
			projectionList.add(Projections.property("requestDate"), "requestDate");
			cr.setProjection(projectionList);
			cr.add(Restrictions.eq("fund.id", fundId));
			cr.add(Restrictions.eq("this.status", "INITIAL"));
			cr.add(Restrictions.le("this.requestDate", valueDate));
			cr.setResultTransformer(Transformers.aliasToBean(CustomerInvestment.class));
			resList = cr.list();			
		}catch(HibernateException he){
			dayEndDto.setSuccess(false);
			dayEndDto.setStatus("Hibernate Error");
			System.out.println(he.getMessage());
			he.printStackTrace();
			return;
		}finally{
			if(session!=null){
				try{
					session.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
		if(resList!=null){
			String res = "";
			DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
			for(CustomerInvestment ci : resList){
				res = res + ci.getInvenstmentCode()+","+ci.getCustomer().getCode()+","+ci.getAmount()+","+df.format(ci.getRequestDate())+";";
			}
			dayEndDto.setResultString(res);
		}
		dayEndDto.setSuccess(true);
	}
	
	@SuppressWarnings("unchecked")
	public void queryPendingRedemptions(int fundId, Date valueDate, DayEndDto dayEndDto){
		List<CustomerRedemptionRequest> resList = null;
		Session session = null;
		try{
			session = HibernateUtil.getSession().openSession();
			Criteria cr = session.createCriteria(CustomerRedemptionRequest.class);
			
			cr.createAlias("fund", "fund");
			ProjectionList projectionList = Projections.projectionList();
			projectionList.add(Projections.property("redemptionCode"), "redemptionCode");
			projectionList.add(Projections.property("customer"), "customer");
			projectionList.add(Projections.property("redemptionType"), "redemptionType");
			projectionList.add(Projections.property("totAmount"), "totAmount");
			projectionList.add(Projections.property("expectedDate"), "expectedDate");
			cr.setProjection(projectionList);
			cr.add(Restrictions.eq("fund.id", fundId));
			cr.add(Restrictions.eq("this.status", "INITIAL"));
			cr.add(Restrictions.le("this.expectedDate", valueDate));
			cr.setResultTransformer(Transformers.aliasToBean(CustomerRedemptionRequest.class));
			resList = cr.list();			
		}catch(HibernateException he){
			dayEndDto.setSuccess(false);
			dayEndDto.setStatus("Hibernate Error");
			System.out.println(he.getMessage());
			he.printStackTrace();
			return;
		}finally{
			if(session!=null){
				try{
					session.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
		if(resList!=null){
			String res = "";
			DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
			for(CustomerRedemptionRequest crr : resList){
				res = res + crr.getRedemptionCode()+","+crr.getCustomer().getCode()+","+crr.getRedemptionType()+","+crr.getTotAmount()+","+df.format(crr.getExpectedDate())+";";
			}
			dayEndDto.setResultString(res);
		}
		dayEndDto.setSuccess(true);
	}
	
	@SuppressWarnings("unchecked")
	public void queryPendingDeals(int fundId, Date valueDate, DayEndDto dayEndDto){
		List<Deal> resList = null;
		Session session = null;
		try{
			session = HibernateUtil.getSession().openSession();
			Criteria cr = session.createCriteria(Deal.class);
			
			cr.createAlias("fund", "fund");
			ProjectionList projectionList = Projections.projectionList();
			projectionList.add(Projections.property("dealType"), "dealType");
			projectionList.add(Projections.property("dealCode"), "dealCode");
			projectionList.add(Projections.property("dealValue"), "dealValue");
			projectionList.add(Projections.property("createdUser"), "createdUser");
			projectionList.add(Projections.property("createdDate"), "createdDate");
			cr.setProjection(projectionList);
			cr.add(Restrictions.eq("fund.id", fundId));
			cr.add(Restrictions.eq("this.status", "Initial"));
			cr.add(Restrictions.eq("this.valueDate", valueDate));
			cr.setResultTransformer(Transformers.aliasToBean(Deal.class));
			resList = cr.list();			
		}catch(HibernateException he){
			dayEndDto.setSuccess(false);
			dayEndDto.setStatus("Hibernate Error");
			System.out.println(he.getMessage());
			he.printStackTrace();
			return;
		}finally{
			if(session!=null){
				try{
					session.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
		if(resList!=null){
			String res = "";
			DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
			for(Deal deal : resList){
				res = res + deal.getDealType().getCode()+","+deal.getDealCode()+","+deal.getDealValue()+","+deal.getCreatedUser()+","+df.format(deal.getCreatedDate())+";";
			}
			dayEndDto.setResultString(res);
		}
		dayEndDto.setSuccess(true);
	}
	
	@SuppressWarnings("unchecked")
	public void queryPendingPayments(int fundId, Date valueDate, DayEndDto dayEndDto){
		List<Payment> resList = null;
		Session session = null;
		try{
			session = HibernateUtil.getSession().openSession();
			Criteria cr = session.createCriteria(Payment.class);
			
			cr.createAlias("fund", "fund");
			ProjectionList projectionList = Projections.projectionList();
			projectionList.add(Projections.property("paymentNo"), "paymentNo");
			projectionList.add(Projections.property("amount"), "amount");
			projectionList.add(Projections.property("reqDate"), "reqDate");

			cr.setProjection(projectionList);
			cr.add(Restrictions.eq("fund.id", fundId));
			cr.add(Restrictions.eq("this.status", (byte)0));
			cr.add(Restrictions.le("this.reqDate", valueDate));
			cr.setResultTransformer(Transformers.aliasToBean(Payment.class));
			resList = cr.list();			
		}catch(HibernateException he){
			dayEndDto.setSuccess(false);
			dayEndDto.setStatus("Hibernate Error");
			System.out.println(he.getMessage());
			he.printStackTrace();
			return;
		}finally{
			if(session!=null){
				try{
					session.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
		if(resList!=null){
			String res = "";
			DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
			for(Payment payment : resList){
				res = res + payment.getPaymentNo()+","+payment.getAmount()+","+df.format(payment.getReqDate())+";";
			}
			dayEndDto.setResultString(res);
		}
		dayEndDto.setSuccess(true);
	}
	
	@SuppressWarnings("unchecked")
	public void queryPendingReceipts(int fundId, Date valueDate, DayEndDto dayEndDto){
		List<Receipt> resList = null;
		Session session = null;
		try{
			session = HibernateUtil.getSession().openSession();
			Criteria cr = session.createCriteria(Receipt.class);
			
			cr.createAlias("fund", "fund");
			ProjectionList projectionList = Projections.projectionList();
			projectionList.add(Projections.property("receiptNo"), "receiptNo");
			projectionList.add(Projections.property("amount"), "amount");
			projectionList.add(Projections.property("reqDate"), "reqDate");

			cr.setProjection(projectionList);
			cr.add(Restrictions.eq("fund.id", fundId));
			cr.add(Restrictions.eq("this.status", (byte)0));
			cr.add(Restrictions.le("this.reqDate", valueDate));
			cr.setResultTransformer(Transformers.aliasToBean(Receipt.class));
			resList = cr.list();			
		}catch(HibernateException he){
			dayEndDto.setSuccess(false);
			dayEndDto.setStatus("Hibernate Error");
			System.out.println(he.getMessage());
			he.printStackTrace();
			return;
		}finally{
			if(session!=null){
				try{
					session.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
		if(resList!=null){
			String res = "";
			DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
			for(Receipt receipt : resList){
				res = res + receipt.getReceiptNo()+","+receipt.getAmount()+","+df.format(receipt.getReqDate())+";";
			}
			dayEndDto.setResultString(res);
		}
		dayEndDto.setSuccess(true);
	}
	
	@SuppressWarnings("unchecked")
	public void queryPendingFloatingRates(int fundId, Date valueDate, DayEndDto dayEndDto){
		List<DealInstrumentMap> resList = null;
		Session session = null;
		try{
			session = HibernateUtil.getSession().openSession();
			Criteria cr = session.createCriteria(DealInstrumentMap.class);
			
			cr.createAlias("deal", "deal");
			cr.createAlias("deal.fund", "fund");
			cr.createAlias("deal.dealType", "dt");
			cr.createAlias("instrument", "instrument");
			cr.createAlias("instrument.instrumentType", "insType");
			
			ProjectionList projectionList = Projections.projectionList();
			projectionList.add(Projections.property("deal"), "deal");
			projectionList.add(Projections.property("instrument"), "instrument");
			cr.setProjection(projectionList);
			cr.add(Restrictions.eq("fund.id", fundId));
			cr.add(Restrictions.eq("dt.code", "PURCH"));
			cr.add(Restrictions.eq("insType.interestRate", InterestRate.V));
			cr.add(Restrictions.le("deal.valueDate", valueDate));
			cr.add(Restrictions.gt("deal.maturityDate", valueDate));
			cr.setResultTransformer(Transformers.aliasToBean(DealInstrumentMap.class));
			resList = cr.list();			
		}catch(HibernateException he){
			dayEndDto.setSuccess(false);
			dayEndDto.setStatus("Hibernate Error");
			System.out.println(he.getMessage());
			he.printStackTrace();
			return;
		}finally{
			if(session!=null){
				try{
					session.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
		if(resList!=null){
			String res = "";
			for(DealInstrumentMap dim : resList){
				Collections.sort(dim.getInstrument().getInstrumentFloatingRates(),new Comparator<InstrumentFloatingRate>() {
					@Override
					public int compare(InstrumentFloatingRate o1,
							InstrumentFloatingRate o2) {
						return o1.getStartingDate().compareTo(o2.getStartingDate());
					}
				});
				Date lastReset = null;
				Date nextReset = null;
				for(InstrumentFloatingRate ifr : dim.getInstrument().getInstrumentFloatingRates()){
					if(ifr.getStartingDate().compareTo(valueDate)>0)
						break;
					lastReset = ifr.getStartingDate();
				}
				if(lastReset!=null){
					Calendar c = Calendar.getInstance();
					c.setTime(lastReset); // Now use today date.
					c.add(Calendar.DATE, dim.getInstrument().getResetPeriod());
					nextReset = c.getTime();
				}
				if(lastReset==null || nextReset.compareTo(valueDate)<=0)
				res = res + dim.getDeal().getDealCode()+","+dim.getInstrument().getInstrumentType().getInstrumentName()+","+dim.getInstrument().getInstrumentCode()+","+dim.getInstrument().getIssuer().getCode()+";";
			}
			dayEndDto.setResultString(res);
		}
		dayEndDto.setSuccess(true);
	}
	
	
	@SuppressWarnings("unchecked")
	//int fundId, Date valueDate, DayEndDto dayEndDto, String dealTypeCode
	public void queryPendingCollaterals(int fundId, Date valueDate, DayEndDto dayEndDto){
		String resultString = "";
		Session session = null;
		List<Deal> dealList = null;
		Transaction tx = null;
		try {
			session = HibernateUtil.getSession().openSession();
			tx = session.beginTransaction();
			
			//SELECT d FROM Deal d LEFT JOIN d.dealInstrumentMaps WHERE d.dealInstrumentMaps IS NULL
			//SELECT d FROM Deal d WHERE size(d.dealInstrumentMaps) = 0
			//SELECT d FROM Deal d WHERE size(d.dealInstrumentMaps) = 0 AND d.valueDate<=:valDate AND d.maturityDate>=:valDate AND d.status='Confirmed' AND d.dealType.code=:dealType AND d.fund.id=:fundId
			Query query = session.createQuery("SELECT d FROM Deal d WHERE size(d.dealInstrumentMaps) = 0 AND d.valueDate<=:valDate AND d.maturityDate>=:valDate AND d.status='Confirmed' AND d.dealType.code=:dealType AND d.fund.id=:fundId");
			query.setParameter("fundId", fundId);
			query.setParameter("dealType", dayEndDto.getQueryTwo());
			query.setParameter("valDate", valueDate);			
			dealList = query.list();			
			tx.commit();
		} catch (Exception e) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByID Fund " + e.getMessage());
			e.printStackTrace();
		} finally {
			session.close();
		}
		if(dealList!=null){
			DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
			for(Deal deal : dealList){
				resultString = resultString + deal.getDealCode()+","+deal.getIssuer().getName()+","+deal.getDealValue()+","+df.format(deal.getValueDate())+";";
			}
		}
		System.out.println(resultString);
		dayEndDto.setResultString(resultString);
	}
	
	@SuppressWarnings("unchecked")
	public List<Deal> getRunningDealList(int fundId, Date valueDate, String dealType){
		List<Deal> dealList = null;
		Session session = null;
		Transaction tx = null;
		try {
			session = HibernateUtil.getSession().openSession();
			tx = session.beginTransaction();
			Query query = session.createQuery("FROM Deal d WHERE d.fund.id=:fundId AND d.dealType.code=:dealType AND d.valueDate<=:valueDate AND d.maturityDate>:valueDate AND status='Confirmed'");
			query.setParameter("dealType", dealType);
			query.setParameter("valueDate", valueDate);
			query.setParameter("fundId", fundId);
			List<Deal> resList = query.list();		
			tx.commit();
			dealList = resList;
		} catch (HibernateException hiEx) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByID Deal " + hiEx.getMessage());
			hiEx.printStackTrace();
		} finally {
			if(session!=null)
				try{
					session.close();
				}catch(Exception e){
					e.printStackTrace();
				}
		}		
		return dealList;
	}
	
	@SuppressWarnings("unchecked")
	public List<Deal> updateMaturityDealStatus(int fundId, Date valueDate, String newStatus){
		List<Deal> dealList = null;
		Session session = null;
		Transaction tx = null;
		try {
			session = HibernateUtil.getSession().openSession();
			tx = session.beginTransaction();
			Query query = session.createQuery("FROM Deal d WHERE d.fund.id=:fundId AND d.maturityDate=:valueDate AND status='Confirmed'");
			query.setParameter("valueDate", valueDate);
			query.setParameter("fundId", fundId);
			List<Deal> resList = query.list();	
			query = session.createQuery("UPDATE Deal d SET d.status=:status WHERE d.fund.id=:fundId AND d.maturityDate=:valueDate AND status='Confirmed'");
			query.setParameter("status", newStatus);
			query.setParameter("valueDate", valueDate);
			query.setParameter("fundId", fundId);
			query.executeUpdate();
			tx.commit();
			dealList = resList;
		} catch (HibernateException hiEx) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByID Deal " + hiEx.getMessage());
			hiEx.printStackTrace();
		} finally {
			if(session!=null)
				try{
					session.close();
				}catch(Exception e){
					e.printStackTrace();
				}
		}		
		return dealList;
	}
	
	
	@SuppressWarnings("unchecked")
	public List<Deal> getMaturityDealList(int fundId, Date valueDate){
		List<Deal> dealList = null;
		
		Session session = null;
		Transaction tx = null;
		try {
			session = HibernateUtil.getSession().openSession();
			tx = session.beginTransaction();
			Query query = session.createQuery("FROM Deal d WHERE d.fund.id=:fundId AND d.maturityDate=:valueDate AND status='Confirmed'");
			query.setParameter("valueDate", valueDate);
			query.setParameter("fundId", fundId);
			List<Deal> resList = query.list();		
			tx.commit();
			dealList = resList;
		} catch (HibernateException hiEx) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByID Deal " + hiEx.getMessage());
			hiEx.printStackTrace();
		} finally {
			if(session!=null)
				try{
					session.close();
				}catch(Exception e){
					e.printStackTrace();
				}
		}		
		return dealList;
	}
	
	public void updateEodParams(Fund fund, DailyFigure dailyFigure, List<DealDailyInterest> dealDailyIntList, List<MoneyTransaction> moneyTransactions, DayEndDto dayEndDto){
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.update(fund);
			session.save(dailyFigure);
			for(DealDailyInterest ddi : dealDailyIntList){
				session.save(ddi);
			}
			for(MoneyTransaction moneyTrans : moneyTransactions){
				session.save(moneyTrans);
			}
			tx.commit();
		} catch (Exception ex) {
			tx.rollback();
			System.out.println("Error in Updating EOD Parameters for the Fund : "+fund.getFundCode());
			ex.printStackTrace();
			dayEndDto.setStatus("Error while EOD Parameter updating");
			dayEndDto.setSuccess(false);
		} finally {
			session.close();
		}		
	}
	
	public static void main(String[] args) throws ParseException {
		DayEndDto dayEndDto = new DayEndDto();
		dayEndDto.setQueryOne("REPO");
		
		//List<Deal> dealList = new DayEndDao().getRunningDealList(24, new Date(), "FD");
/*		List<Deal> dealList = new DayEndDao().updateMaturityDealStatus(24, new Date(), "TTTT");
		if(dealList!=null)
		for(Deal deal : dealList){
			System.out.println(deal.getDealCode());
		}*/
		
		Date valueDate = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse("2014-11-14 00:00:00");
		double sum = new DayEndDao().getMoneyTransSumForEod(24, new Date());
		System.out.println("SUMMM: "+sum);
		
		double totUnits = new DayEndDao().getUnitSumForEod(24, valueDate);
		System.out.println("Total Units: "+totUnits);
	}

}
